﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Text;
using System.Timers;
using System.Windows.Forms;
using Npgsql;

namespace Thesis_v01
{
   public class DBConnection
   {
      private string server;
      private string port;
      private string userID;
      private string password;
      private string database;

      private NpgsqlConnection longRunConn;
      //private Timer clock;

      public DBConnection()
      {
         server = ConfigurationSettings.AppSettings["ServerAddress"];
         port = ConfigurationSettings.AppSettings["ServerPort"];
         userID = ConfigurationSettings.AppSettings["UserID"];
         password = ConfigurationSettings.AppSettings["Password"];
         database = ConfigurationSettings.AppSettings["DBName"];

         //SetTimer();
      }

      /*private void SetTimer()
      {
         clock = new Timer();
         clock.Interval = 5000;
      }*/

      public void OnlyOpenConnection()
      {
         try
         {
            // PostgreSQL connection string
            string connectionString = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4}", server, port, userID, password, database);

            // Connect with Npgsql provider
            longRunConn = new NpgsqlConnection(connectionString);
            longRunConn.Open();
         }
         catch (Exception error)
         {
            Console.WriteLine(error.ToString());

            throw;
         }
      }

      public void OnlyCloseConnection()
      {
         try
         {
            longRunConn.Close();
         }
         catch (Exception error)
         {
            Console.WriteLine(error.ToString());

            throw;
         }
      }

      public LoginDetail AttemptLogin(string username, string userPassword)
      {
         LoginDetail newDetails = new LoginDetail();

         try
         {
            // PostgreSQL connection string
            string connectionString = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4}", server, port, userID, password, database);

            // Connect with Npgsql provider
            NpgsqlConnection conn = new NpgsqlConnection(connectionString);
            conn.Open();

            // make sql query
            string sql = "select * from CheckLoginDetails('" + username + "','" + userPassword + "')";
            //MessageBox.Show(sql);

            // perform query
            NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);

            // store result
            DataSet ds = new DataSet();
            da.Fill(ds);

            DataTable dt = ds.Tables[0];
            object data0 = dt.Rows[0].ItemArray[0];
            object data1 = dt.Rows[0].ItemArray[1];
            object data2 = dt.Rows[0].ItemArray[2];
            string user = data0.ToString();
            string result = data1.ToString();
            string description = data2.ToString();

            newDetails.UserID = Int32.Parse(user);

            //mainWindow.InstructorID = Int32.Parse(user);

            // display result
            if (Boolean.Parse(result))
            {
               newDetails.Success = true;
               newDetails.Description = description + ", user = " + user;
               //MessageBox.Show(description + ", user = " + user);
            }
            else
            {
               newDetails.Success = false;
               newDetails.Description = description;
               //MessageBox.Show(description);
            }
            //MessageBox.Show(result + " & " + description);

            // close the connection
            conn.Close();
         }
         catch (Exception error)
         {
            newDetails.Success = false;
            Console.WriteLine(error.ToString());
            string displayError = error.InnerException.Message;
            displayError += "\n\nMake sure that:\n" +
               "1. The application settings are correct\n" +
               "2. There is an active network connection\n" +
               "3. The database server is running properly\n";
            MessageBox.Show(displayError, "DB Error", MessageBoxButtons.OK, MessageBoxIcon.Error);

            //throw;
         }
         //finally
         //{
         //   newDetails = null;
         //}

         return newDetails;
      }

      public ArrayList RetrieveCourses(int ID)
      {
         ArrayList retrievedCourses = new ArrayList();

         try
         {
            // PostgreSQL connection string
            string connectionString = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4}", server, port, userID, password, database);

            // Connect with Npgsql provider
            NpgsqlConnection conn = new NpgsqlConnection(connectionString);
            conn.Open();

            // make sql query
            string sql = "select * from RetrieveCourses('" + ID + "')";
            //MessageBox.Show(sql);

            // perform query
            NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);

            // store result
            DataSet ds = new DataSet();
            da.Fill(ds);

            DataTable dt = ds.Tables[0];
            foreach (DataRow row in dt.Rows)
            {
               object data0 = row.ItemArray[0];
               object data1 = row.ItemArray[1];
               object data2 = row.ItemArray[2];
               object data3 = row.ItemArray[3];
               object data4 = row.ItemArray[4];
               object data5 = row.ItemArray[5];

               CourseDetail temp = new CourseDetail();
               temp.CourseID = Int32.Parse(data0.ToString());
               temp.SubjectCode = data1.ToString();
               temp.SubjectTitle = "";
               temp.CourseSession = data2.ToString();
               //temp.CourseYear = Int32.Parse(data3.ToString());
               temp.TermStart = ParseTimeStamp(data3.ToString());
               temp.TermFinish = ParseTimeStamp(data4.ToString());
               temp.Password = data5.ToString();

               retrievedCourses.Add(temp);
            }

            //MessageBox.Show(result + " & " + description);

            // close the connection
            conn.Close();
         }
         catch (Exception error)
         {
            Console.WriteLine(error.ToString());

            throw;
         }

         return retrievedCourses;
      }

      public ArrayList RetrieveWeeks(int courseID, int instructorID)
      {
         ArrayList retrievedWeeks = new ArrayList();

         try
         {
            // PostgreSQL connection string
            string connectionString = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4}", server, port, userID, password, database);

            // Connect with Npgsql provider
            NpgsqlConnection conn = new NpgsqlConnection(connectionString);
            conn.Open();

            // make sql query
            string sql = "select * from RetrieveWeeks('" + courseID + "','" + instructorID + "')";
            //MessageBox.Show(sql);

            // perform query
            NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);

            // store result
            DataSet ds = new DataSet();
            da.Fill(ds);

            DataTable dt = ds.Tables[0];
            foreach (DataRow row in dt.Rows)
            {
               object data0 = row.ItemArray[0];

               retrievedWeeks.Add(Int32.Parse(data0.ToString()));
            }

            // close the connection
            conn.Close();
         }
         catch (Exception error)
         {
            Console.WriteLine(error.ToString());

            throw;
         }

         return retrievedWeeks;
      }

      public ArrayList RetrieveLessons (int courseID, int instructorID, int week)
      {
         ArrayList retrievedLessons = new ArrayList();

         try
         {
            // PostgreSQL connection string
            string connectionString = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4}", server, port, userID, password, database);

            // Connect with Npgsql provider
            NpgsqlConnection conn = new NpgsqlConnection(connectionString);
            conn.Open();

            // make sql query
            string sql = "select * from RetrieveClasses('" + courseID + "','" + instructorID + "','" + week + "')";

            // perform query
            NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);

            // store result
            DataSet ds = new DataSet();
            da.Fill(ds);

            DataTable dt = ds.Tables[0];
            foreach (DataRow row in dt.Rows)
            {
               object data0 = row.ItemArray[0];
               object data1 = row.ItemArray[1];
               object data2 = row.ItemArray[2];
               object data3 = row.ItemArray[3];
               object data4 = row.ItemArray[4];
               object data5 = row.ItemArray[5];
               //object data6 = row.ItemArray[6];

               ClassDetail tempClass = new ClassDetail();
               tempClass.ClassID = Int32.Parse(data0.ToString());
               tempClass.CourseID = Int32.Parse(data1.ToString());
               tempClass.InstructorID = Int32.Parse(data2.ToString());
               tempClass.StartTime = ParseTimeStamp(data3.ToString());
               tempClass.FinishTime = ParseTimeStamp(data4.ToString());
               tempClass.DayOfWeek = tempClass.StartTime.DayOfWeek;
               tempClass.Week = Int32.Parse(data5.ToString());

               retrievedLessons.Add(tempClass);
            }

            // close the connection
            conn.Close();
         }
         catch (Exception error)
         {
            Console.WriteLine(error.ToString());

            throw;
         }

         return retrievedLessons;
      }

      public ArrayList RetrieveQuestions (int classID)
      {
         ArrayList retrievedQuestions = new ArrayList();

         try
         {
            // PostgreSQL connection string
            string connectionString = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4}", server, port, userID, password, database);

            // Connect with Npgsql provider
            NpgsqlConnection conn = new NpgsqlConnection(connectionString);
            conn.Open();

            // make sql query
            string sql = "select * from RetrieveQuestions('" + classID + "')";

            // perform query
            NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);

            // store result
            DataSet ds = new DataSet();
            da.Fill(ds);

            DataTable dt = ds.Tables[0];
            foreach (DataRow row in dt.Rows)
            {
               object data0 = row.ItemArray[0];
               object data1 = row.ItemArray[1];
               object data2 = row.ItemArray[2];
               object data3 = row.ItemArray[3];
               object data4 = row.ItemArray[4];

               QuestionDetail tempQuestion = new QuestionDetail();
               tempQuestion.ID = Int32.Parse(data0.ToString());
               tempQuestion.Class = Int32.Parse(data1.ToString());
               tempQuestion.Number = Int32.Parse(data2.ToString());
               tempQuestion.Type = ParseQuestionType(data3.ToString());
               tempQuestion.Question = data4.ToString();

               string sql2 = "select * from RetrievePossibleAnswers('" + tempQuestion.ID + "')";

               // perform query
               NpgsqlDataAdapter da2 = new NpgsqlDataAdapter(sql2, conn);

               // store result
               DataSet ds2 = new DataSet();
               da2.Fill(ds2);
               DataTable dt2 = ds2.Tables[0];

               foreach (DataRow row2 in dt2.Rows)
               {
                  object tempAnswer = row2.ItemArray[0];
                  tempQuestion.AddAnswer(tempAnswer.ToString());
               }

               sql2 = "select * from RetrieveCorrectAnswers('" + tempQuestion.ID + "')";

               // perform query
               da2 = new NpgsqlDataAdapter(sql2, conn);

               // store result
               ds2 = new DataSet();
               da2.Fill(ds2);
               dt2 = ds2.Tables[0];

               foreach (DataRow row2 in dt2.Rows)
               {
                  object tempAnswer = row2.ItemArray[0];
                  tempQuestion.AddCorrect(tempAnswer.ToString());
               }

               retrievedQuestions.Add(tempQuestion);
            }

            // close the connection
            conn.Close();
         }
         catch (Exception error)
         {
            Console.WriteLine(error.ToString());

            throw;
         }

         return retrievedQuestions;
      }

      public int InsertQuestion(QuestionDetail question)
      {
         int index = 0;

         try
         {
            // PostgreSQL connection string
            string connectionString = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4}", server, port, userID, password, database);

            // Connect with Npgsql provider
            NpgsqlConnection conn = new NpgsqlConnection(connectionString);
            conn.Open();

            // make question insert string
            string sql = "insert into Question (class, number, qType, description) values (" +
               question.Class + "," +
               question.Number + ",'" +
               ParseQuestionTypeEnum(question.Type) + "','" +
               question.Question + "')" + 
               "returning id";

            // perform question insert
            NpgsqlCommand command = new NpgsqlCommand(sql, conn);
            string result = command.ExecuteScalar().ToString();

            index = Int32.Parse(result);

            if (question.CorrectAnswers[0].ToString() != String.Empty)
            {

            bool firstComma = true;
            //if (question.Type != QuestionType.Open)
            //{
               // make answer insert string
               sql = "insert into Answer (question, possibleAns) values ";
               foreach (string answer in question.PossibleAnswers)
               {
                  if (firstComma)
                  {
                     firstComma = false;
                  }
                  else
                  {
                     sql += ",";
                  }

                  sql += ("(" + index + ",'" + answer + "')");
               }

               // perform answer inserts
               command = new NpgsqlCommand(sql, conn);
               command.ExecuteNonQuery();
            //}

            
               string insertCorrectSql = "insert into CorrectAnswer (question, answer) values ";
               firstComma = true;

               sql = "select * from RetrieveAnswerIDDescription(" + index + ")";
               command = new NpgsqlCommand(sql, conn);
               NpgsqlDataReader dr = command.ExecuteReader();
               while (dr.Read())
               {
                  foreach (string correct in question.CorrectAnswers)
                  {
                     if (dr[1].ToString() == correct)
                     {
                        if (firstComma)
                        {
                           firstComma = false;
                        }
                        else
                        {
                           insertCorrectSql += ",";
                        }

                        insertCorrectSql += ("(" + index + "," + dr[0] + ")");
                     }
                  }
               }

               command = new NpgsqlCommand(insertCorrectSql, conn);
               command.ExecuteNonQuery();
            }

            // close the connection
            conn.Close();
         }
         catch (Exception error)
         {
            Console.WriteLine(error.ToString());

            throw;
         }

         return index;
      }

      //public int InsertPossibleAnswers(int questionID, ArrayList answers)
      //{
      //   int index = 0;
      //   //int rowsaffected;

      //   try
      //   {
      //      // PostgreSQL connection string
      //      string connectionString = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4}", server, port, userID, password, database);

      //      // Connect with Npgsql provider
      //      NpgsqlConnection conn = new NpgsqlConnection(connectionString);
      //      conn.Open();


      //      // close the connection
      //      conn.Close();
      //   }
      //   catch (Exception error)
      //   {
      //      Console.WriteLine(error.ToString());

      //      throw;
      //   }

      //   return index;
      //}

      public void UpdateQuestion(QuestionDetail question)
      {
         try
         {
            // PostgreSQL connection string
            string connectionString = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4}", server, port, userID, password, database);

            // Connect with Npgsql provider
            NpgsqlConnection conn = new NpgsqlConnection(connectionString);
            conn.Open();

            // make sql query
            string sql = "update Question set qType = '" +
               ParseQuestionTypeEnum(question.Type) + "', description = '" +
               question.Question + "' where id = " + question.ID;

            // perform query
            NpgsqlCommand command = new NpgsqlCommand(sql, conn);
            command.ExecuteNonQuery();

            // perform insert
            sql = "delete from CorrectAnswer where question = " + question.ID;
            command = new NpgsqlCommand(sql, conn);
            command.ExecuteNonQuery();

            // perform insert
            sql = "delete from Answer where question = " + question.ID;
            command = new NpgsqlCommand(sql, conn);
            command.ExecuteNonQuery();

            bool firstComma = true;
            sql = "insert into Answer (question, possibleAns) values ";
            foreach (string answer in question.PossibleAnswers)
            {
               if (firstComma)
               {
                  firstComma = false;
               }
               else
               {
                  sql += ",";
               }

               sql += ("(" + question.ID + ",'" + answer + "')");
            }

            // perform answer inserts
            command = new NpgsqlCommand(sql, conn);
            command.ExecuteNonQuery();

            string insertCorrectSql = "insert into CorrectAnswer (question, answer) values ";
            firstComma = true;

            sql = "select * from RetrieveAnswerIDDescription(" + question.ID + ")";
            command = new NpgsqlCommand(sql, conn);
            NpgsqlDataReader dr = command.ExecuteReader();
            while (dr.Read())
            {
               foreach (string correct in question.CorrectAnswers)
               {
                  if (dr[1].ToString() == correct)
                  {
                     if (firstComma)
                     {
                        firstComma = false;
                     }
                     else
                     {
                        insertCorrectSql += ",";
                     }

                     insertCorrectSql += ("(" + question.ID + "," + dr[0] + ")");
                  }
               }
            }

            command = new NpgsqlCommand(insertCorrectSql, conn);
            command.ExecuteNonQuery();

            // close the connection
            conn.Close();
         }
         catch (Exception error)
         {
            Console.WriteLine(error.ToString());

            throw;
         }
      }

      //public void UpdatePossibleAnswers(int questionID, ArrayList answers)
      //{
      //   try
      //   {
      //      // PostgreSQL connection string
      //      string connectionString = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4}", server, port, userID, password, database);

      //      // Connect with Npgsql provider
      //      NpgsqlConnection conn = new NpgsqlConnection(connectionString);
      //      conn.Open();

      //      // make sql query
      //      string sql = "delete from Answer where question = " + questionID;

      //      // perform insert
      //      NpgsqlCommand command = new NpgsqlCommand(sql, conn);
      //      command.ExecuteNonQuery();

      //      // close the connection
      //      conn.Close();
      //   }
      //   catch (Exception error)
      //   {
      //      Console.WriteLine(error.ToString());

      //      throw;
      //   }

      //   //InsertPossibleAnswers(questionID, answers);
      //}

      public void DeleteQuestion(QuestionDetail question)
      {
         try
         {
            // PostgreSQL connection string
            string connectionString = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4}", server, port, userID, password, database);

            // Connect with Npgsql provider
            NpgsqlConnection conn = new NpgsqlConnection(connectionString);
            conn.Open();

            // make sql query
            string sql = "delete from CorrectAnswer where question = " + question.ID;

            // perform insert
            NpgsqlCommand command = new NpgsqlCommand(sql, conn);
            command.ExecuteNonQuery();

            // make sql query
            sql = "delete from Answer where question = " + question.ID;

            // perform insert
            command = new NpgsqlCommand(sql, conn);
            command.ExecuteNonQuery();

            sql = "delete from Question where id = " + question.ID;

            command = new NpgsqlCommand(sql, conn);
            command.ExecuteNonQuery();

            // close the connection
            conn.Close();
         }
         catch (Exception error)
         {
            Console.WriteLine(error.ToString());

            throw;
         }
      }

      // assumes db connection already opened
      public ArrayList RetrieveOneResponse(int questionID, int startingID)
      {
         ArrayList response = new ArrayList();
         try
         {
            string sql = "select * from RetrieveOnlyOneResponse(" + questionID + "," + startingID + ")";

            NpgsqlCommand command = new NpgsqlCommand(sql, longRunConn);
            NpgsqlDataReader dr = command.ExecuteReader();
            //Console.WriteLine("test 1");
            while (dr.Read())
            {
               //Console.WriteLine("test 2");
               //foreach (string entry in dr)
               //{
               //   Console.WriteLine(entry);
               //   response.Add(entry);
               //}
               for (int i = 0; i < dr.FieldCount; i++)
               {
                  response.Add(dr[i].ToString());
               }
            }
         }
         catch (Exception error)
         {
            Console.WriteLine(error.ToString());

            throw;
         }

         return response;
      }

      // assumes db connection already opened
      public int FindMaxResponseID()
      {
         int max = 0;

         try
         {
            string sql = "select * from FindMaxResponseID";

            NpgsqlCommand command = new NpgsqlCommand(sql, longRunConn);

            max = Int32.Parse(command.ExecuteScalar().ToString());

         }
         catch (Exception error)
         {
            Console.WriteLine(error.ToString());

            throw;
         }

         return max;
      }

      public ArrayList RetrieveEnquiries(int classID)
      {
         ArrayList retrievedEnquiries = new ArrayList();

         try
         {
            // PostgreSQL connection string
            string connectionString = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4}", server, port, userID, password, database);

            // Connect with Npgsql provider
            NpgsqlConnection conn = new NpgsqlConnection(connectionString);
            conn.Open();

            // make sql query
            string sql = "select * from RetrieveEnquiries(" + classID + ")";

            // perform query
            NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);

            // store result
            DataSet ds = new DataSet();
            da.Fill(ds);

            DataTable dt = ds.Tables[0];
            foreach (DataRow row in dt.Rows)
            {
               object data0 = row.ItemArray[0];

               retrievedEnquiries.Add(data0.ToString());
            }

            // close the connection
            conn.Close();
         }
         catch (Exception error)
         {
            Console.WriteLine(error.ToString());

            throw;
         }

         return retrievedEnquiries;
      }

      public void UpdateCoursePassword(int id, string updatedPassword)
      {
         try
         {
            string connectionString = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4}", server, port, userID, password, database);

            // Connect with Npgsql provider
            NpgsqlConnection conn = new NpgsqlConnection(connectionString);
            conn.Open();

            string sql = "update Course set password = '" + updatedPassword + "' where id = " + id;

            // perform query
            NpgsqlCommand command = new NpgsqlCommand(sql, conn);
            command.ExecuteNonQuery();
         }
         catch (Exception error)
         {
            Console.WriteLine(error.ToString());

            throw;
         }
      }

      private DateTime ParseTimeStamp(string time)
      {
         //return DateTime.ParseExact(time, "d/MM/yyyy h:mm:ss tt", null);
         return DateTime.Parse(time);
      }

      private QuestionType ParseQuestionType(string stringType)
      {
         QuestionType type = QuestionType.MCQ;

         switch (stringType)
         {
            case "MCQ":
               type = QuestionType.MCQ;
               break;
            case "Open":
               type = QuestionType.Open;
               break;
            case "TFQ":
               type = QuestionType.TFQ;
               break;
         }

         return type;
      }

      private string ParseQuestionTypeEnum(QuestionType type)
      {
         string temp = "Open";

         switch (type)
         {
            case QuestionType.MCQ:
               temp = "MCQ";
               break;
            case QuestionType.Open:
               temp = "Open";
               break;
            case QuestionType.TFQ:
               temp = "TFQ";
               break;
         }

         return temp;
      }

   }
}
